<?php
/**
 * @Author:      lyt
 * @DateTime:    2017-01-09 14:51:25
 * @Description: 添加新的报表服务提供方法
 */
class ReportService extends Eloquent{

    /**
     * @Author:      lyt
     * @DateTime:    2017-01-09 15:03:31
     * @Description: 来广营业绩的报表
     */
    public static function get_bpjx_detail($begin_date,$end_date,$shop_id){
        $insurance_detail = array();
        // $temp_filit=DB::select("SELECT sc3.* FROM service_classification sc3 LEFT JOIN service_classification sc2 on sc3.parent_id=sc2.id LEFT JOIN service_classification sc on sc2.parent_id=sc.id where sc3.`status`=1 and sc.id in(13,14,1533)");
        // $filit=array();
        // foreach ($temp_filit as $_tf) {
        //     $filit[]=$_tf->id;
        // }

        $in=DB::table("order_performance")
            ->leftJoin('orders', 'orders.order_id', '=', 'order_performance.order_id')
            ->leftJoin('teams', 'teams.id', '=', 'order_performance.team_id')
            ->leftJoin('team_type', 'team_type.id', '=', 'order_performance.process_id');
        if($shop_id){
            $in=$in->where("orders.shop_id",$shop_id);
        }
        $temp_order_product=array();
        $in=$in->whereBetween("order_performance.created_at",array($begin_date,$end_date))
            ->select("order_performance.*","orders.status as o_status","teams.name as team_name","team_type.name as team_type_name")->get();           
        foreach ($in as $_os) {
            $temp=str_replace("[","",$_os->order_product_id);
            $temp=str_replace("]","",$temp);
            $temp=str_replace('"',"",$temp);
            $array=explode(',',$temp);
            $tem_op=DB::table("order_product")
            ->leftJoin('product_package', 'product_package.id', '=', 'order_product.product_id')
            ->leftJoin('service', 'service.id', '=', 'order_product.product_id')
            // ->where("order_product.status",1)
            ->whereIn("order_product.id",$array)
            ->whereIn("order_product.product_type",array("base","non_standard"))
            ->select("order_product.*","service.classify as s_c","product_package.classify as p_c")->get();
            $engernd_arr=DB::table("order_performance_employee")
            ->leftJoin('maintenance_engineers', 'maintenance_engineers.id', '=', 'order_performance_employee.user_id')
            ->leftJoin('sc_user', 'sc_user.id', '=', 'order_performance_employee.user_id')
            ->where("order_performance_employee.order_id",$_os->order_id)
            ->where("order_performance_employee.process_id",$_os->process_id)
            ->select("order_performance_employee.user_id","maintenance_engineers.name as me_name","sc_user.real_name as sc_name","order_performance_employee.user_type")->get();
            foreach ($tem_op as $_in) {
                
                $classify=$_in->product_type=="base"?$_in->p_c:$_in->s_c;
                $service_classification = Report::get_service_classification($classify);  
                $_insurance_detaill = array(
                    'id' => $_os->id,//绩效id
                    'order_product_id' => $_in->id,//服务包id
                    'order_product_name' => $_in->product_name,//服务包id
                    'order_id' => $_os->order_id,//订单号
                    'order_status' => $_os->o_status,//订单号
                    'team_id' => $_os->team_id,//班组id
                    'team_name' => $_os->team_name,//班组名字
                    'process_id' => $_os->process_id,//工序id
                    'team_type_name' => $_os->team_type_name,//工序名字 
                    'created_at' => $_os->created_at,//工序名字 
                    'engernd_arr' => $engernd_arr,//技师信息
                    'level_1_id' => $service_classification['level_1_id'],
                    'level_1_name' => $service_classification['level_1_name'],
                    'level_2_id' => $service_classification['level_2_id'],
                    'level_2_name' => $service_classification['level_2_name'],
                    'level_3_id' => $service_classification['level_3_id'],
                    'level_3_name' => $service_classification['level_3_name'],
                    'record_type' => $_os->type,//0代表增项  1代表 减项

                );
                array_push($insurance_detail,$_insurance_detaill);
            }
        }         
        return $insurance_detail;
    }


    /**
     * @Author:      lyt
     * @DateTime:    2017-01-09 15:03:31
     * @Description: 来广营业绩的报表
     */
    public static function yingshou($shop_id){       
        //订单 
        $in_sql="SELECT
                DATE_FORMAT(o.pay_time,'%Y-%m') as temp_data,o.pay_user_id,sum(opr.amount) amount
            FROM
                order_pay_record opr
            LEFT JOIN orders o ON o.order_id = opr.order_id
            LEFT JOIN users ON users.id = o.pay_user_id
            WHERE o.place = 2
            AND o.`status` IN (1018,1017,1217,1317,1417) and  opr.`status` = 1
            AND opr.pay_type = 6
            AND users.type = 2
            and o.verify_status=0  ";
        //洗车单
        $wash_order="SELECT DATE_FORMAT(cwo.pay_time,'%Y-%m') as temp_data,cwo.user_id as pay_user_id,sum(cwopr.amount) as amount 
                FROM  car_wash_order_pay_record cwopr 
                LEFT JOIN car_wash_order cwo ON cwo.order_id = cwopr.order_id 
                LEFT JOIN users u ON u.id = cwo.user_id 
                WHERE  cwopr.pay_type = 6 AND cwopr.`status` = 1 AND u.type = 2 AND cwo.verify_status = 0 and cwo.`status`=1417 ";
        //套餐卡单        
        $service_order="SELECT DATE_FORMAT(sco.pay_time,'%Y-%m') as temp_data,sco.user_id as pay_user_id,sum(cwopr.amount) as amount 
                FROM  service_card_order_pay_record cwopr 
                LEFT JOIN service_card_order sco ON sco.order_id = cwopr.order_id 
                LEFT JOIN users u ON u.id = sco.user_id 
                WHERE  cwopr.pay_type = 6 AND cwopr.`status` = 1 AND u.type = 2 AND sco.verify_status = 0 and sco.`status`=1417 ";
        //订单退单
        $out_sql="SELECT
                DATE_FORMAT(ro.implement_time,'%Y-%m') as temp_data,o.pay_user_id,sum(ror.amount) amount
            FROM
                return_order ro
            LEFT JOIN orders o ON o.order_id = ro.order_id
            LEFT JOIN users ON users.id = o.pay_user_id
            LEFT JOIN return_order_record ror on ror.return_order_id=ro.id
            WHERE o.place = 2
            and ro.`status` = 1
            AND ror.pay_type = 6
            AND users.type = 2
            and ro.verify_status=0 ";

        //洗车单退单
        $re_wash_order="SELECT  DATE_FORMAT(rco.implement_time,'%Y-%m') as temp_data,cwo.user_id as pay_user_id,sum(rwr.amount) as amount
                FROM `return_washorder_record` rwr
                LEFT JOIN return_carwash_order rco ON rco.id = rwr.return_washorder_id
                LEFT JOIN car_wash_order cwo ON cwo.order_id = rco.order_id
                LEFT JOIN users u ON u.id = cwo.user_id where rwr.pay_type=6 AND rco.`status` = 1 and u.type=2 and rco.verify_status=0 ";
        //套餐卡单    退单    
        $re_service_order="SELECT DATE_FORMAT(rsco.implement_time,'%Y-%m') as temp_data,sco.user_id as pay_user_id,sum(rscr.amount) as amount
                FROM
                return_service_card_record rscr
                LEFT JOIN return_service_card_order rsco ON rsco.id = rscr.return_order_id
                LEFT JOIN service_card_order sco ON sco.order_id = rsco.order_id
                left join users on users.id= sco.user_id where rscr.pay_type=6 and users.type=2 and rsco.status=1 and sco.verify_status=0  ";

        if($shop_id){
            $in_sql=$in_sql." and  o.shop_id=".$shop_id;
            $out_sql=$out_sql." and o.shop_id=".$shop_id;
            $wash_order=$wash_order." and  cwo.shop_id=".$shop_id;
            $service_order=$service_order." and sco.shop_id=".$shop_id;
            $re_wash_order=$re_wash_order." and  cwo.shop_id=".$shop_id;
            $re_service_order=$re_service_order."and  sco.shop_id=".$shop_id;
        }
        
        $in=DB::select($in_sql." group by pay_user_id,temp_data");//查出所有的订单欠款数据
        $in_array=array();
        foreach ($in as $_in) {
            if(isset($in_array[$_in->pay_user_id])){
                $in_array[$_in->pay_user_id][$_in->temp_data]=$_in->amount;
            }else{
                $in_array[$_in->pay_user_id]=array($_in->temp_data=>$_in->amount);
            }
        }
        //根据分组每个月买个企业每个点
        $reurn=DB::select($out_sql." group by pay_user_id,temp_data");        
        foreach ($reurn as $_reurn) {
            if(isset($in_array[$_reurn->pay_user_id])){
                if(isset($in_array[$_reurn->pay_user_id][$_reurn->temp_data])){
                    $in_array[$_reurn->pay_user_id][$_reurn->temp_data]=$in_array[$_reurn->pay_user_id][$_reurn->temp_data]-$_reurn->amount;
                }else{
                    $in_array[$_reurn->pay_user_id][$_reurn->temp_data]=-$_reurn->amount;
                }
            }else{
                $in_array[$_reurn->pay_user_id]=array($_reurn->temp_data=>-$_reurn->amount);
            }
        }
        //洗车单
        $wash_order_arr=DB::select($wash_order." group by pay_user_id,temp_data");        
        foreach ($wash_order_arr as $_woa) {
            if(isset($in_array[$_woa->pay_user_id])){
                if(isset($in_array[$_woa->pay_user_id][$_woa->temp_data])){
                    $in_array[$_woa->pay_user_id][$_woa->temp_data]=$in_array[$_woa->pay_user_id][$_woa->temp_data]+$_woa->amount;
                }else{
                    $in_array[$_woa->pay_user_id][$_woa->temp_data]=$_woa->amount;
                }
            }else{
                $in_array[$_woa->pay_user_id]=array($_woa->temp_data=>$_woa->amount);
            }
        }
        //套餐卡订单
        $ser_order_arr=DB::select($service_order." group by pay_user_id,temp_data");        
        foreach ($ser_order_arr as $_woa) {
            if(isset($in_array[$_woa->pay_user_id])){
                if(isset($in_array[$_woa->pay_user_id][$_woa->temp_data])){
                    $in_array[$_woa->pay_user_id][$_woa->temp_data]=$in_array[$_woa->pay_user_id][$_woa->temp_data]+$_woa->amount;
                }else{
                    $in_array[$_woa->pay_user_id][$_woa->temp_data]=$_woa->amount;
                }
            }else{
                $in_array[$_woa->pay_user_id]=array($_woa->temp_data=>$_woa->amount);
            }
        }


        $reurn_car=DB::select($re_wash_order." group by pay_user_id,temp_data");        
        foreach ($reurn_car as $_reurn) {
            if(isset($in_array[$_reurn->pay_user_id])){
                if(isset($in_array[$_reurn->pay_user_id][$_reurn->temp_data])){
                    $in_array[$_reurn->pay_user_id][$_reurn->temp_data]=$in_array[$_reurn->pay_user_id][$_reurn->temp_data]-$_reurn->amount;
                }else{
                    $in_array[$_reurn->pay_user_id][$_reurn->temp_data]=-$_reurn->amount;
                }
            }else{
                $in_array[$_reurn->pay_user_id]=array($_reurn->temp_data=>-$_reurn->amount);
            }
        }

        $reurn_ser=DB::select($re_service_order." group by pay_user_id,temp_data");        
        foreach ($reurn_ser as $_reurn) {
            if(isset($in_array[$_reurn->pay_user_id])){
                if(isset($in_array[$_reurn->pay_user_id][$_reurn->temp_data])){
                    $in_array[$_reurn->pay_user_id][$_reurn->temp_data]=$in_array[$_reurn->pay_user_id][$_reurn->temp_data]-$_reurn->amount;
                }else{
                    $in_array[$_reurn->pay_user_id][$_reurn->temp_data]=-$_reurn->amount;
                }
            }else{
                $in_array[$_reurn->pay_user_id]=array($_reurn->temp_data=>-$_reurn->amount);
            }
        }

        //计算期初
        $begin_sql="select sum(current_amount) amount,company_id from begin_arrear ";
        if($shop_id){
            $begin_sql=$begin_sql.'where shop_id= '.$shop_id;
        }
        $begin_sql=$begin_sql.' GROUP BY company_id';
        $begin=DB::select($begin_sql);    
        $begin_array=array();
        foreach ($begin as $_b) {
            $begin_array[$_b->company_id]=$_b->amount;
        }   
        $compaany=Companys::all();
        $temp_order_product=array();
        foreach ($compaany as $_c){
            $ua=UserAccount::where("user_id",$_c->id)->first();
            $jingshouren=$xingdongfangan='';
            if($shop_id){
                $c=ShopUserProperty::where("shop_id",$shop_id)->where("user_id",$_c->id)->first();
                $xingdongfangan=isset($c)?$c->user_comments:"";
                $sc=DB::select("SELECT group_concat(real_name) as real_name FROM `sc_user` where shop_id=? and title=1 and `status`=1",array($shop_id));
                $jingshouren=$sc[0]->real_name;
            }
            $_insurance_detaill = array(
                'company_id' => $_c->id,//企业id
                'name' => $_c->name,//企业名字 
                'e_type' => $_c->e_type,//企业性质
                'account_period' => $_c->account_period,//账期
                'is_agree' => $_c->is_agree,//合同 1 已签署，2 未签署
                'contacts' => $_c->contacts,//联系人
                'phone' => $_c->contact_phone,//联系电话
                'jingshouren' => $jingshouren,//经手人
                'xingdongfangan' => $xingdongfangan,//行动方案 
                "zhangqi"=>$_c->account_period,//账期
                "credit"=>isset($ua)?$ua->credit:0,//额度
                "begin_amount"=>isset($begin_array[$_c->id])?$begin_array[$_c->id]:0,
                "qiankuan"=>isset($in_array[$_c->id])?$in_array[$_c->id]:array(),
            );
            array_push($temp_order_product,$_insurance_detaill);
        }            
        return $temp_order_product;
    }
 
}